import re
from datetime import datetime
from wordcloud import WordCloud
import jieba
import pandas as pd
import pymysql
from matplotlib import pyplot as plt


def get_con():
    conn = pymysql.Connect(host="127.0.0.1",user="root",
                          password="hhxxttxs.lj",db="scrapy_demo",charset='utf8')
    cursor = conn.cursor()
    return conn,cursor
def close_conn(conn,cursor):
    cursor.close()
    conn.close()
def query(sql,*args):

    conn,cursor = get_con()
    cursor.execute(sql,args)
    res = cursor.fetchall()
    close_conn(conn,cursor)
    return res
def get_data():
    sql ="select * from stock_comment"
    res = query(sql)
    return res

def date_transfrom(res):
    ##print(res)
    res = res.replace('月盘前'," ")
    res = res.replace('月盘后'," ")
    ##print(res)
    day = res[:2]
    month = res[2:4]
    time = res[4:]
    formatted_string = f"{str(datetime.now().year)} {month} {day} {time}"
    ##print(formatted_string)

    datetime_object = datetime.strptime(formatted_string,'%Y %m %d %H:%M:%S')

    return datetime_object

def cutWords(content):
    wordlist_jieba=jieba.lcut(content)
    return wordlist_jieba
   ## print(wordlist_jieba)

if __name__=='__main__':
    text = get_data()
    text = pd.DataFrame(text,columns=['id','date','title','comment'])
    result =text['date']
    result = result.apply(date_transfrom)
    text['date'] = result
    text.drop(labels=['id','date'],axis=1,inplace=True) ## 这里删除列 若axis = 0 则删除行 inplace 是否就地修改
    ## print(result)
    ##print(text)
    content =text.sum()
    title = content.title
    comment = content.comment
    ## 去除，。等
    pattern = r'[a-zA-Z0-9\u4e00-\u9fff]+' ## 中文字符的unicode 范围

    title = re.findall(pattern,title,re.S)
    comment = re.findall(pattern,comment,re.S)
    title="".join(title)
    comment="".join(comment)
    ##print(title)

    title = cutWords(title)
    comment = cutWords(comment)
    while '盘前' in title:
        title.remove('盘前')
    while '盘后' in title:
        title.remove('盘后')
    space_title =' '.join(title)

    while '市场' in comment:
        comment.remove('市场')
    while '表现' in comment:
        comment.remove('表现')
    space_comment =' '.join(comment)
    print(space_comment)

    WC = WordCloud(
        max_words=100,
        height=400,  # 输出图像高度
        background_color='white',  # 背景颜色
        font_path='msyh.ttc',  # 中文字体路径，确保正确显示中文字符 [ty-reference](15)
        mode='RGBA',
        mask=None
    )

    WC.generate(space_title)
    WC.generate(space_comment)
    plt.figure(figsize=(6,6),dpi=600)
    plt.axis("off")
    plt.imshow(WC)




    ##print(result)

